/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.folha.dao;

import br.com.folha.entity.GradeHoraria;
import br.com.folha.entity.Professor;
import br.com.folha.entity.Turma;
import br.com.folha.entity.Unidade;
import br.com.folha.utilidades.HibernateUtil;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
import javax.persistence.NoResultException;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;

/**
 *
 * @author marceloml
 */
public class GradeDao implements Serializable {
    
    private static final long serialVersionUID = 1L;
    private Long resultado;
    
    public List<GradeHoraria> consultaGrade(Unidade unidade, Date dataIni, Date dataFim){
        
        Session sessao = HibernateUtil.getSessionFactory();
        Criteria criteria = sessao.createCriteria(GradeHoraria.class); 
        criteria.createAlias("turma", "t");
        criteria.add(Restrictions.ge("data", dataIni)).add(Restrictions.le("data", dataFim)).add(Restrictions.eq("unidade", unidade));                
        criteria.addOrder(Order.asc("data"));
        criteria.addOrder(Order.asc("t.nome"));
        criteria.addOrder(Order.asc("horaIni"));
        criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
        
       return criteria.list();
        
    }
    
    public List<GradeHoraria> consultaGradeHora(Unidade unidade, Date dataIni, Date dataFim, Date horaIni, Date horaFim){
        
        Session sessao = HibernateUtil.getSessionFactory();
        Criteria criteria = sessao.createCriteria(GradeHoraria.class); 
        criteria.createAlias("turma", "t");
        criteria.add(Restrictions.ge("data", dataIni)).add(Restrictions.le("data", dataFim)).add(Restrictions.eq("unidade", unidade)).add(Restrictions.ge("horaIni", horaIni)).add(Restrictions.le("horaFim", horaFim));
        criteria.addOrder(Order.asc("data"));
        criteria.addOrder(Order.asc("horaIni"));
        criteria.addOrder(Order.asc("t.nome"));
        criteria.addOrder(Order.asc("horaIni"));
        criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
        
       return criteria.list();
        
    }    
    
    public List<Object> listaGradePf(Unidade unidade){
        Session sessao = HibernateUtil.getSessionFactory();
        Criteria criteria = sessao.createCriteria(GradeHoraria.class);
        criteria.createAlias("unidade", "u");
        criteria.createAlias("professor", "p");
        criteria.createAlias("turma", "t");  
        
        criteria.add(Restrictions.eq("unidade", unidade));
        
        criteria.addOrder(Order.asc("p.nome"));
        criteria.addOrder(Order.asc("data"));
        criteria.addOrder(Order.asc("t.nome"));
        criteria.addOrder(Order.asc("horaIni"));        
        
        criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);        

        List lista = criteria.list();        
        sessao.close();
        return lista;                        
    }     
    
    public Long consultaAulaProfessor(Professor professor, Date dia, Date horaini, Date horafim) {
        Session sessao = HibernateUtil.getSessionFactory();        
        
         Query query  = sessao.createQuery("select count(*) as contador from GradeHoraria where cd_professor=:pprofessor and data =:pdia and ((hora_ini between :phoraini and :phorafim) or (hora_fim between :phoraini and :phorafim))");
       
        query.setParameter("pprofessor",professor);        
        query.setParameter("pdia",dia);
        query.setParameter("phoraini",horaini);
        query.setParameter("phorafim",horafim);
            try{
             resultado=(Long) query.list().get(0);
            }catch(NoResultException e){
                resultado=null;
            }finally{
                sessao.close();
            }
            return resultado;       
    } 
    
    public Long consultaTurma(Turma turma, Date diaIni, Date diaFim) {
        Session sessao = HibernateUtil.getSessionFactory();        
        
         Query query  = sessao.createQuery("select count(*) as contador from GradeHoraria where turma=:pturma and data >=:pdiaIni and data <=:pdiaFim");
       
        query.setParameter("pturma",turma);        
        query.setParameter("pdiaIni",diaIni);
        query.setParameter("pdiaFim",diaFim);
        
            try{
             resultado=(Long) query.list().get(0);
            }catch(NoResultException e){
                resultado=null;
            }finally{
                sessao.close();
            }
            return resultado;       
    }    
    
public Long consultaAulaTurma(Turma turma, Date dia, Date horaini, Date horafim) {
        Session sessao = HibernateUtil.getSessionFactory();        
        
         Query query  = sessao.createQuery("select count(*) as contador from GradeHoraria where turma=:pturma and data =:pdia and ((hora_ini between :phoraini and :phorafim) or (hora_fim between :phoraini and :phorafim))");
       
        query.setParameter("pturma",turma);        
        query.setParameter("pdia",dia);
        query.setParameter("phoraini",horaini);
        query.setParameter("phorafim",horafim);
            try{
             resultado=(Long) query.list().get(0);
            }catch(NoResultException e){
                resultado=null;
            }finally{
                sessao.close();
            }
            return resultado;       
    }

    public Integer buscaUltRegistro(){
        
        Session sessao = HibernateUtil.getSessionFactory();        
        Integer ultId;
        Query query  = sessao.createQuery("SELECT max(id) AS ID from GradeHoraria order by id desc limit 1;");

            try{
             ultId = (Integer)query.list().get(0);
            }catch(NoResultException e){
                ultId=0;
            }finally{
                sessao.close();
            }        
        
        return ultId;
    }
    
}
